package com.power.sifa.approve.dao.impl;

import com.power.sifa.statistics.model.vo.BaseCountVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import com.power.sifa.approve.dao.Apl_mainDaoCustom;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 业务申请主表 持久层实现(hibernate)
 */

@Slf4j
@Repository
public class Apl_mainDaoImpl implements Apl_mainDaoCustom {

    @Resource
    private EntityManager entityManager;

    @Override
    public Integer queryMaxno(String yyyymmdd) {
        Integer max = 0;
        String sql = "select max(a.applycode) from Apl_main a where a.applycode like ?1";
        Query query = entityManager.createQuery(sql);
        query.setParameter(1, yyyymmdd + "%");
        List li = query.getResultList();
        if (li != null && li.size() > 0) {
            String code = (String) li.get(0);
            if (code != null) {
                max = Integer.parseInt(code.substring(8));
            }
        }
        return max;
    }

    @Override
    public Integer queryMaxAcceptno(String yyyymmdd) {
        Integer max = 0;
        String sql = "select max(a.acceptcode) from Apl_main a where a.acceptcode like ?1 and a.acceptcode like ?2";
//		String sql="select max(a.acceptcode) from Not_notice a where a.acceptcode like ?1 and a.type = ?2";
        Query query = entityManager.createQuery(sql);
        query.setParameter(1, yyyymmdd + "%");
        query.setParameter(2, "%受第%号");
//		query.setParameter(2, Tp_notice.ACCEPT);
        List li = query.getResultList();
        if (li != null && li.size() > 0) {
            String code = (String) li.get(0);
            if (code != null) {
                max = Integer.parseInt(code.substring(10, 15));
            }
        }
        return max;
    }

    @Override
    public Integer queryMaxStopno(String yyyymmdd) {
        Integer max = 0;
        String sql = "select max(a.acceptcode) from Apl_main a where a.acceptcode like ?1 and a.acceptcode like ?2";
//		String sql="select max(a.acceptcode) from Not_notice a where a.acceptcode like ?1 and a.type = ?2";
        Query query = entityManager.createQuery(sql);
        query.setParameter(1, "%" + yyyymmdd + "%");
        query.setParameter(2, "赣药监受理%号");
//		query.setParameter(2, Tp_notice.STOP);
        List li = query.getResultList();
        if (li != null && li.size() > 0) {
            String code = (String) li.get(0);
            if (code != null) {
                max = Integer.parseInt(code.substring(15, 18));
            }
        }
        return max;
    }

    @Override
    public List<BaseCountVO> caseStatus(String acceptadid) {
        Query query = null;
        String sql = "SELECT substring(subtime, 1, 4) AS key, '收到' AS name, COUNT(*) AS count " +
                "FROM apl_main " +
                "WHERE 1 = 1 AND applycode IS NOT NULL AND subtime IS NOT NULL " +
                "AND matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " AND acceptadid = ?";
        }

        sql += " GROUP BY key " +
                "UNION ALL " +
                "SELECT substring(subtime, 1, 4) AS key, '受理' AS name, COUNT(*) AS count " +
                "FROM apl_main " +
                "WHERE 1 = 1 AND applycode IS NOT NULL AND status IN (6000606, 6000608, 6000609) " +
                "AND matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " AND acceptadid = ?";
        }

        sql += " GROUP BY key " +
                "UNION ALL " +
                "SELECT substring(subtime, 1, 4) AS key, '受前调解' AS name, COUNT(*) AS count " +
                "FROM apl_main am " +
                "LEFT JOIN apl_admreview aa ON am.id = aa.id " +
                "WHERE 1 = 1 AND am.applycode IS NOT NULL AND am.accepttime IS NULL AND aa.status = 6002906" +
                "AND am.matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " AND acceptadid = ?";
        }

        sql += " GROUP BY key ORDER BY key desc ";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter(1, acceptadid);
            query.setParameter(2, acceptadid);
            query.setParameter(3, acceptadid);
        }

        List<Object[]> results = query.getResultList();
        List<BaseCountVO> result = new ArrayList<>();
        for (Object[] row : results) {
            BaseCountVO baseCount = new BaseCountVO();
            baseCount.setKey((String) row[0]);
            baseCount.setName((String) row[1]);
            baseCount.setCount(((BigInteger) row[2]).longValueExact());
            result.add(baseCount);
        }
        return result;
    }

    @Override
    public List<BaseCountVO> caseReceptionStatus(String acceptadid, String year) {
        Query query = null;
        String sql = "select substring(subtime, 6, 2) as key, 'receivedNum' AS name, count(*) as count " +
                "from apl_main " +
                "where applycode IS NOT NULL AND subtime IS NOT NULL " +
                "and matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(year)) {
            sql += " and substring(subtime, 1, 4) = :year";
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }

        sql += " group by key union all" +
                " select substring(subtime, 6, 2) as key, 'acceptedNum' AS name, count(*) as count" +
                " from apl_main" +
                " where applycode IS NOT NULL AND status IN (6000606, 6000608, 6000609)" +
                " and matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(year)) {
            sql += " and substring(subtime, 1, 4) = :year";
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }

        sql += " group by key order by name, key";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(year)) {
            query.setParameter("year", year);
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter("acceptadid", acceptadid);
        }

        List<Object[]> results = query.getResultList();
        List<BaseCountVO> result = new ArrayList<>();
        for (Object[] row : results) {
            BaseCountVO baseCount = new BaseCountVO();
            baseCount.setKey((String) row[0]);
            baseCount.setName((String) row[1]);
            baseCount.setCount(((BigInteger) row[2]).longValueExact());
            result.add(baseCount);
        }
        return result;
    }

    @Override
//    public BaseGraphVO countCaseType(String acceptadid) {
    public Map<String, Long> countCaseType(String acceptadid, String year) {
        Query query = null;
        String sql = "select aa.prtype AS key, count(*) count " +
                "from apl_main am left join apl_admreview aa on am.id = aa.id " +
                "where am.applycode is not null " +
                "and matid = '100000_360100-000201009000-QT-002-02'";

        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }

        if (StringUtils.isNotEmpty(year)) {
            sql += " and substring(subtime, 1, 4) = :year";
        }

        sql += " group by key order by count desc";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(year)) {
            query.setParameter("year", year);
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter("acceptadid", acceptadid);
        }

        List<Object[]> results = query.getResultList();
        Map<String, Long> map = new HashMap<>();
        for (Object[] row : results) {
            long value = ((BigInteger) row[1]).longValue();
            if (row[0] == null) {
                map.put("6003910", value);
            } else {
                map.put(row[0].toString(), value);
            }
        }
        return map;
    }

    @Override
    public List<BaseCountVO> countTypeLVOne(String acceptadid, String year) {
        Query query = null;
        String sql = "select count.parentid, ad.name, sum(count.num) " +
                "from (select aa.decisiontype id, ad.parentid parentid, count(*) num " +
                "from apl_main am " +
                "left join apl_admreview aa on am.id = aa.id " +
                "left join apl_decistiontype ad on aa.decisiontype = ad.id " +
                "where am.applycode is not null " +
                "and aa.decisiontype is not null " +
                "and matid = '100000_360100-000201009000-QT-002-02'";
        if (StringUtils.isNotEmpty(year)) {
            sql += " and substring(aa.decisiondate, 1, 4) = :year";
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }
        sql += " group by aa.decisiontype, ad.parentid) count" +
                " left join apl_decistiontype ad on count.parentid = ad.id" +
                " where count.parentid is not null" +
                " group by count.parentid, ad.name";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(year)) {
            query.setParameter("year", year);
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter("acceptadid", acceptadid);
        }
        List<Object[]> results = query.getResultList();
        List<BaseCountVO> result = new ArrayList<>();
        for (Object[] row : results) {
            BaseCountVO baseCount = new BaseCountVO();
            baseCount.setKey(row[0].toString());
            baseCount.setName(row[1].toString());
            baseCount.setCount(((BigDecimal) row[2]).longValueExact());
            result.add(baseCount);
        }
        return result;
    }

    @Override
    public List<BaseCountVO> countTypeLVTwo(String acceptadid, String year) {
        Query query = null;
        String sql = "select aa.decisiontype id, ad.name name, count(*) count " +
                "from apl_main am " +
                "left join apl_admreview aa on am.id = aa.id " +
                "left join apl_decistiontype ad on aa.decisiontype = ad.id " +
                "where am.applycode is not null " +
                "and aa.decisiontype is not null " +
                "and matid = '100000_360100-000201009000-QT-002-02'";
        if (StringUtils.isNotEmpty(year)) {
            sql += " and substring(aa.decisiondate, 1, 4) = :year";
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }
        sql += " group by aa.decisiontype, ad.name";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(year)) {
            query.setParameter("year", year);
        }
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter("acceptadid", acceptadid);
        }
        List<Object[]> results = query.getResultList();
        List<BaseCountVO> result = new ArrayList<>();
        for (Object[] row : results) {
            BaseCountVO baseCount = new BaseCountVO();
            baseCount.setKey(row[0].toString());
            baseCount.setName(row[1].toString());
            baseCount.setCount(((BigInteger) row[2]).longValueExact());
            result.add(baseCount);
        }
        return result;
    }

    @Override
    public List<BaseCountVO> countReceiptData(String acceptadid) {
        Query query = null;
        String sql = "select substring(aa.mgrcate, 1, 2) mg, sm.name, count(*) count " +
                "from apl_main am " +
                "left join apl_admreview aa on am.id = aa.id " +
                "left join std_mgrcate sm on substring(aa.mgrcate, 1, 2) = sm.id " +
                "where am.applycode is not null " +
                "and substring(aa.mgrcate, 1, 2) is not null " +
                "and sm.name is not null ";
        if (StringUtils.isNotEmpty(acceptadid)) {
            sql += " and acceptadid = :acceptadid";
        }
        sql += " group by mg, sm.name order by count desc";

        query = entityManager.createNativeQuery(sql);
        if (StringUtils.isNotEmpty(acceptadid)) {
            query.setParameter("acceptadid", acceptadid);
        }
        List<Object[]> results = query.getResultList();
        List<BaseCountVO> result = new ArrayList<>();
        for (Object[] row : results) {
            BaseCountVO baseCount = new BaseCountVO();
            baseCount.setKey(row[0].toString());
            baseCount.setName(row[1].toString());
            baseCount.setCount(((BigInteger) row[2]).longValueExact());
            result.add(baseCount);
        }
        return result;
    }

}
